{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d5665017-908d-4fa6-bfc1-14c3f97a1487",
   "metadata": {},
   "source": [
    "#### Objective:- Randomly assign race to individuals using computed race migration counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "51cced6a-1c1f-495f-b544-c55644fc9ae1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import cudf, cupy as cp\n",
    "import os,random\n",
    "import pandas as pd, numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "853667f4-8e20-498d-9b90-bddc61539d6e",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Race assignment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "05afd4bb-3f5b-4ed0-8c1e-198e6f9361ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>P_delta</th>\n",
       "      <th>R1_diff</th>\n",
       "      <th>R2_diff</th>\n",
       "      <th>R3_diff</th>\n",
       "      <th>R4_diff</th>\n",
       "      <th>R5_diff</th>\n",
       "      <th>R6_diff</th>\n",
       "      <th>R7_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-10</td>\n",
       "      <td>-16</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>4</td>\n",
       "      <td>-10</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>-23</td>\n",
       "      <td>-21</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>-2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001005</td>\n",
       "      <td>-8</td>\n",
       "      <td>-7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20  P_delta  R1_diff  R2_diff  R3_diff  R4_diff  R5_diff  \\\n",
       "0  10010201001000      -10      -16        2        0        0        0   \n",
       "1  10010201001001        4      -10        9        0        2        0   \n",
       "2  10010201001002      -23      -21       -2        0        0        0   \n",
       "3  10010201001003        4        5       -2        0        0        0   \n",
       "4  10010201001005       -8       -7        0        0        0        0   \n",
       "\n",
       "   R6_diff  R7_diff  \n",
       "0        0        4  \n",
       "1        1        2  \n",
       "2        0        0  \n",
       "3        0        1  \n",
       "4        0       -1  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "races = cudf.read_csv('data/mapped_data_with_race.csv',usecols=['ID20','P_delta','R1_diff','R2_diff','R3_diff','R4_diff','R5_diff','R6_diff','R7_diff'])\n",
    "races.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e24a60c2-3d72-4466-bae6-989663acb115",
   "metadata": {},
   "outputs": [],
   "source": [
    "races = races.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2797ad14-c9c3-442d-8149-409ecf6a7966",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>P_delta</th>\n",
       "      <th>R1_diff</th>\n",
       "      <th>R2_diff</th>\n",
       "      <th>R3_diff</th>\n",
       "      <th>R4_diff</th>\n",
       "      <th>R5_diff</th>\n",
       "      <th>R6_diff</th>\n",
       "      <th>R7_diff</th>\n",
       "      <th>pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-10</td>\n",
       "      <td>[-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -...</td>\n",
       "      <td>[2, 2]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[7, 7, 7, 7]</td>\n",
       "      <td>[-1, -1, -1, -1, -1, -1, -1, 7, 7, 2, -1, -1, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>4</td>\n",
       "      <td>[-1, -1, -1, -1, -1, -1, -1, -1, -1, -1]</td>\n",
       "      <td>[2, 2, 2, 2, 2, 2, 2, 2, 2]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[4, 4]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[6]</td>\n",
       "      <td>[7, 7]</td>\n",
       "      <td>[-1, -1, -1, 7, -1, -1, 6, 7, 2, -1, -1, 2, 2,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>-23</td>\n",
       "      <td>[-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -...</td>\n",
       "      <td>[-2, -2]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[-1, -1, -2, -1, -1, -1, -1, -1, -1, -1, -1, -...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>4</td>\n",
       "      <td>[1, 1, 1, 1, 1]</td>\n",
       "      <td>[-2, -2]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[7]</td>\n",
       "      <td>[1, 1, 7, 1, 1, -2, 1, -2]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001005</td>\n",
       "      <td>-8</td>\n",
       "      <td>[-1, -1, -1, -1, -1, -1, -1]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[-7]</td>\n",
       "      <td>[-1, -1, -1, -7, -1, -1, -1, -1]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20  P_delta                                            R1_diff  \\\n",
       "0  10010201001000      -10  [-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -...   \n",
       "1  10010201001001        4           [-1, -1, -1, -1, -1, -1, -1, -1, -1, -1]   \n",
       "2  10010201001002      -23  [-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -...   \n",
       "3  10010201001003        4                                    [1, 1, 1, 1, 1]   \n",
       "4  10010201001005       -8                       [-1, -1, -1, -1, -1, -1, -1]   \n",
       "\n",
       "                       R2_diff R3_diff R4_diff R5_diff R6_diff       R7_diff  \\\n",
       "0                       [2, 2]      []      []      []      []  [7, 7, 7, 7]   \n",
       "1  [2, 2, 2, 2, 2, 2, 2, 2, 2]      []  [4, 4]      []     [6]        [7, 7]   \n",
       "2                     [-2, -2]      []      []      []      []            []   \n",
       "3                     [-2, -2]      []      []      []      []           [7]   \n",
       "4                           []      []      []      []      []          [-7]   \n",
       "\n",
       "                                                 pop  \n",
       "0  [-1, -1, -1, -1, -1, -1, -1, 7, 7, 2, -1, -1, ...  \n",
       "1  [-1, -1, -1, 7, -1, -1, 6, 7, 2, -1, -1, 2, 2,...  \n",
       "2  [-1, -1, -2, -1, -1, -1, -1, -1, -1, -1, -1, -...  \n",
       "3                         [1, 1, 7, 1, 1, -2, 1, -2]  \n",
       "4                   [-1, -1, -1, -7, -1, -1, -1, -1]  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "races['R1_diff'] =races['R1_diff'].apply(lambda x : [1]*abs(x) if x>0 else ([-1]*abs(x) if x<0 else []) )\n",
    "races['R2_diff'] =races['R2_diff'].apply(lambda x : [2]*abs(x) if x>0 else ([-2]*abs(x) if x<0 else []) )\n",
    "races['R3_diff'] =races['R3_diff'].apply(lambda x : [3]*abs(x) if x>0 else ([-3]*abs(x) if x<0 else []) )\n",
    "races['R4_diff'] =races['R4_diff'].apply(lambda x : [4]*abs(x) if x>0 else ([-4]*abs(x) if x<0 else []) )\n",
    "races['R5_diff'] =races['R5_diff'].apply(lambda x : [5]*abs(x) if x>0 else ([-5]*abs(x) if x<0 else []) )\n",
    "races['R6_diff'] =races['R6_diff'].apply(lambda x : [6]*abs(x) if x>0 else ([-6]*abs(x) if x<0 else []) )\n",
    "races['R7_diff'] =races['R7_diff'].apply(lambda x : [7]*abs(x) if x>0 else ([-7]*abs(x) if x<0 else []) )\n",
    "races['pop'] = races['R1_diff']+races['R2_diff']+races['R3_diff']+races['R4_diff']+races['R5_diff']+races['R6_diff']+races['R7_diff']\n",
    "races['pop'] = races['pop'].apply(lambda x: random.sample(x,len(x))) # shuffle races\n",
    "races.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ac6dc6f8-286d-415e-86e2-2b758469d16d",
   "metadata": {},
   "outputs": [],
   "source": [
    "gpu_races = cudf.from_pandas(races[['ID20','pop']])\n",
    "del(races)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7a85a112-e7a6-4777-a7e0-b6665165d791",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20  pop\n",
       "0  10010201001000   -1\n",
       "1  10010201001000   -1\n",
       "2  10010201001000   -1\n",
       "3  10010201001000   -1\n",
       "4  10010201001000   -1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gpu_races = gpu_races.explode('pop').reset_index(drop=True)\n",
    "gpu_races.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "bbd8c93a-d9fa-455d-9432-5dc04337edb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# gpu_races.to_pandas().to_csv('data/full_races_assigned.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1af81855-12ae-49f1-8a85-f4957f88ee18",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Concat races"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "eec06600-836f-43bb-b81f-17ef6e0c05e8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20  pop\n",
       "0  10010201001000   -1\n",
       "1  10010201001000   -1\n",
       "2  10010201001000   -1\n",
       "3  10010201001000   -1\n",
       "4  10010201001000   -1"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "races = pd.read_csv('data/full_races_assigned.csv').drop('Unnamed: 0',axis=1)\n",
    "races = cudf.from_pandas(races)\n",
    "races.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bb635b23-8763-48b7-9890-59fabe4f54c7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.480590</td>\n",
       "      <td>32.469173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.478140</td>\n",
       "      <td>32.470337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.478485</td>\n",
       "      <td>32.471490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.479645</td>\n",
       "      <td>32.469475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.479910</td>\n",
       "      <td>32.471940</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20          x          y\n",
       "0  10010201001000 -86.480590  32.469173\n",
       "1  10010201001000 -86.478140  32.470337\n",
       "2  10010201001000 -86.478485  32.471490\n",
       "3  10010201001000 -86.479645  32.469475\n",
       "4  10010201001000 -86.479910  32.471940"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population = pd.read_csv('data/final_data_with_race.csv').drop('Unnamed: 0',axis=1)\n",
    "population = cudf.from_pandas(population)\n",
    "population.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bc627911-4d35-43e5-b8a8-d51d8f73dcd8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.480590</td>\n",
       "      <td>32.469173</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.478140</td>\n",
       "      <td>32.470337</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.478485</td>\n",
       "      <td>32.471490</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.479645</td>\n",
       "      <td>32.469475</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.479910</td>\n",
       "      <td>32.471940</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20          x          y  pop\n",
       "0  10010201001000 -86.480590  32.469173   -1\n",
       "1  10010201001000 -86.478140  32.470337   -1\n",
       "2  10010201001000 -86.478485  32.471490   -1\n",
       "3  10010201001000 -86.479645  32.469475   -1\n",
       "4  10010201001000 -86.479910  32.471940   -1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "population_with_race = cudf.concat([population,races['pop']],axis=1)\n",
    "population_with_race.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8654eaae-e095-471d-8681-6e696ce17bd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = population_with_race.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1d9642e9-f9bd-4bb9-83df-958471084621",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp.to_csv('data/population_race_concatenated.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed6679bf-5f0a-4782-959e-0174dbe4a294",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Prepare final dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a79d0275-23e2-40bb-851d-6c8df361fcb3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.480598</td>\n",
       "      <td>32.469173</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.478142</td>\n",
       "      <td>32.470341</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.478485</td>\n",
       "      <td>32.471493</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.479652</td>\n",
       "      <td>32.469475</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>-86.479912</td>\n",
       "      <td>32.471939</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20          x          y  pop\n",
       "0  10010201001000 -86.480598  32.469173   -1\n",
       "1  10010201001000 -86.478142  32.470341   -1\n",
       "2  10010201001000 -86.478485  32.471493   -1\n",
       "3  10010201001000 -86.479652  32.469475   -1\n",
       "4  10010201001000 -86.479912  32.471939   -1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop = dask_cudf.read_csv('data/population_race_concatenated.csv',usecols=['ID20','x','y','pop'],dtype={'ID20':'int64','x':'float32','y':'float32','pop':'int32'})\n",
    "# pop = cudf.from_pandas(pop)\n",
    "pop.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c5af0f6d-6a89-44be-99ce-151d8e618ab3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>P_delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10010201001000</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10010201001001</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10010201001002</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10010201001003</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10010201001005</td>\n",
       "      <td>1</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>-8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20  STATE          COUNTY  P_delta\n",
       "0  10010201001000      1  Autauga County      -10\n",
       "1  10010201001001      1  Autauga County        4\n",
       "2  10010201001002      1  Autauga County      -23\n",
       "3  10010201001003      1  Autauga County        4\n",
       "4  10010201001005      1  Autauga County       -8"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('data/blocks_with_attr.csv',encoding='unicode_escape',usecols=['ID20','STATE','COUNTY','P_delta'],dtype={'ID20':'int64','STATE':'int32','COUNTY':'str','P_delta':'int32'})\n",
    "# df = cudf.from_pandas(df)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cca4e5c7-0763-47b2-956b-df947e3e26ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.COUNTY.replace({r'[^\\x00-\\x7F]+':''},regex=True,inplace=True)\n",
    "df.COUNTY.replace({r'([A-Z][a-z]+)([A-Z]+)':r'\\1'},regex=True,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3890b761-ea06-4db3-a1e9-1a5ec723797e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = cudf.from_pandas(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "afdcb6b1-6a4a-4510-be3d-020ca5c9aeee",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = dask_cudf.from_cudf(df,npartitions=33)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7ea77875-8625-4982-a679-7f5b49beed34",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6194258"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "34cd9165-d48a-4cb4-9d6d-7cfde3225837",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Split dataset to manage OOM\n",
    "# concat_data1 = pd.DataFrame()\n",
    "# concat_data2 = pd.DataFrame()\n",
    "# concat_data3 = pd.DataFrame()\n",
    "# concat_data4 = pd.DataFrame()\n",
    "# concat_data5 = pd.DataFrame()\n",
    "# concat_data12 = None\n",
    "# concat_data34 = None\n",
    "# concat_data12345 = None\n",
    "\n",
    "# def prepare_final_data(i,pop,df):\n",
    "#     global concat_data1\n",
    "#     global concat_data2\n",
    "#     global concat_data3\n",
    "#     global concat_data4\n",
    "#     global concat_data5\n",
    "#     global concat_data12\n",
    "#     global concat_data34\n",
    "#     global concat_data12345\n",
    "\n",
    "#     pop = cudf.from_pandas(pop)\n",
    "#     df = cudf.from_pandas(df)                      \n",
    "#     merged_data = pop.merge(df,on='ID20',how='left').sort_values('ID20')\n",
    "#     # print(merged_data.head())\n",
    "#     del(pop,df)\n",
    "#     if i <= 12:\n",
    "#         concat_data1 = pd.concat([concat_data1,merged_data.to_pandas()])\n",
    "#     elif i <= 24:\n",
    "#         concat_data2 = pd.concat([concat_data2,merged_data.to_pandas()])\n",
    "#         if i== 24:\n",
    "#             concat_data12 = pd.concat([concat_data1,concat_data2])\n",
    "#             concat_data12.to_csv('data/concat_data12.csv')\n",
    "#             del(concat_data1,concat_data2)    \n",
    "#     elif i <= 36:\n",
    "#         concat_data3 = pd.concat([concat_data3,merged_data.to_pandas()])\n",
    "#     elif i <= 47:\n",
    "#         concat_data4 = pd.concat([concat_data4,merged_data.to_pandas()])\n",
    "#         if i == 47:\n",
    "#             concat_data34 = pd.concat([concat_data3,concat_data4])\n",
    "#             concat_data34.to_csv('data/concat_data34.csv')\n",
    "#             del(concat_data3,concat_data4)\n",
    "#     else: \n",
    "#         concat_data5 = pd.concat([concat_data5,merged_data.to_pandas()]) \n",
    "#         if i == 72:\n",
    "#             concat_data5.to_csv('data/concat_data5.csv')\n",
    "#             concat12345 = pd.concat([concat_data34,concat_data5])\n",
    "#             del(concat_data12,concat_data34,concat_data5)\n",
    "\n",
    "# states = {25:\"MA\",26:\"MI\",27:\"MN\",\n",
    "#           28:\"MS\",29:\"MO\",30:\"MT\",31:\"NE\",32:\"NV\",33:\"NH\",34:\"NJ\",35:\"NM\",36:\"NY\",37:\"NC\",38:\"ND\",39:\"OH\",\n",
    "#           40:\"OK\",41:\"OR\",42:\"PA\",44:\"RI\",45:\"SC\",46:\"SD\",47:\"TN\",48:\"TX\",49:\"UT\",50:\"VT\",51:\"VA\",53:\"WA\",\n",
    "#           54:\"WV\",55:\"WI\",56:\"WY\",72:\"PR\"}\n",
    "\n",
    "# for i in states.keys():\n",
    "#     print(i)\n",
    "#     l1 = int(str(i)+'0'*13)\n",
    "#     l2 = int(str(i+1)+'0'*13)\n",
    "#     pop1 = pop[(pop.ID20>=l1) & (pop.ID20<=l2)]\n",
    "#     df1 = df[df.STATE==i]\n",
    "#     prepare_final_data(i,pop1,df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "659bdffd-b487-424c-963a-7dc59f369296",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID20</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>pop</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>P_delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10730129081005</td>\n",
       "      <td>-86.799530</td>\n",
       "      <td>33.390617</td>\n",
       "      <td>-6</td>\n",
       "      <td>1</td>\n",
       "      <td>Jefferson County</td>\n",
       "      <td>-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10730141062002</td>\n",
       "      <td>-87.081055</td>\n",
       "      <td>33.341869</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Jefferson County</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10550108002031</td>\n",
       "      <td>-85.940010</td>\n",
       "      <td>34.133358</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Etowah County</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10810418022002</td>\n",
       "      <td>-85.227203</td>\n",
       "      <td>32.743073</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>Lee County</td>\n",
       "      <td>-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10730120031020</td>\n",
       "      <td>-86.797600</td>\n",
       "      <td>33.596527</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>Jefferson County</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             ID20          x          y  pop  STATE            COUNTY  P_delta\n",
       "0  10730129081005 -86.799530  33.390617   -6      1  Jefferson County      -16\n",
       "1  10730141062002 -87.081055  33.341869    1      1  Jefferson County       49\n",
       "2  10550108002031 -85.940010  34.133358    1      1     Etowah County       26\n",
       "3  10810418022002 -85.227203  32.743073    7      1        Lee County      -20\n",
       "4  10730120031020 -86.797600  33.596527    6      1  Jefferson County       24"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset = pop.merge(df,on='ID20',how='left')\n",
    "dataset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e87d5eda-2b52-4c64-aad0-07c1e33e7621",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/nishant/miniconda3/envs/rapids-22.06/lib/python3.9/site-packages/dask/dataframe/core.py:3950: UserWarning: \n",
      "You did not provide metadata, so Dask is running your function on a small dataset to guess output types. It is possible that Dask will guess incorrectly.\n",
      "To provide an explicit output types or to silence this message, please provide the `meta=` keyword, as described in the map or apply function that you are using.\n",
      "  Before: .apply(func)\n",
      "  After:  .apply(func, meta=('P_delta', 'int64'))\n",
      "\n",
      "  warnings.warn(meta_warning(meta))\n"
     ]
    }
   ],
   "source": [
    "dataset['P_net'] = dataset['P_delta'].apply(lambda x: -1 if x < 0 else ( 1 if x>0 else 0))\n",
    "dataset = dataset.reset_index(drop=True)\n",
    "dataset = dataset.rename(columns ={'pop':'race','ID20':'blockid','STATE':'state','P_delta':'block_diff','COUNTY':'county','P_net':'block_net'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "ae31471d-5693-4f07-ab8a-6b1d2dd1dba8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "182532663\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>blockid</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>race</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>block_diff</th>\n",
       "      <th>block_net</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10330207031015</td>\n",
       "      <td>-87.647072</td>\n",
       "      <td>34.736504</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>Colbert County</td>\n",
       "      <td>-42</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10030116011047</td>\n",
       "      <td>-87.660904</td>\n",
       "      <td>30.471989</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>122</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10439654023028</td>\n",
       "      <td>-86.754951</td>\n",
       "      <td>34.006104</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Cullman County</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10510305002045</td>\n",
       "      <td>-85.966774</td>\n",
       "      <td>32.588291</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Elmore County</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10150003001064</td>\n",
       "      <td>-85.829102</td>\n",
       "      <td>33.667320</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>Calhoun County</td>\n",
       "      <td>-16</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          blockid          x          y  race  state          county  \\\n",
       "0  10330207031015 -87.647072  34.736504    -1      1  Colbert County   \n",
       "1  10030116011047 -87.660904  30.471989     1      1  Baldwin County   \n",
       "2  10439654023028 -86.754951  34.006104     1      1  Cullman County   \n",
       "3  10510305002045 -85.966774  32.588291     2      1   Elmore County   \n",
       "4  10150003001064 -85.829102  33.667320    -1      1  Calhoun County   \n",
       "\n",
       "   block_diff  block_net  \n",
       "0         -42         -1  \n",
       "1         122          1  \n",
       "2          28          1  \n",
       "3          11          1  \n",
       "4         -16         -1  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(len(dataset))\n",
    "dataset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "81d9a559-456b-4507-8a3b-c7547390c792",
   "metadata": {},
   "outputs": [],
   "source": [
    "# dataset.to_parquet('data/census_migration_dataset.parquet')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
